﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

using AmkeniCrossLoginApplication.Data_Access;
using Microsoft.Reporting.WebForms;

namespace Amkeni_System.Report
{
    public partial class PDFCreator : System.Web.UI.Page
    {
        Amkeni_DatabaseEntities db = new Amkeni_DatabaseEntities();

        public void CreatePDF(int ip, int quarter, int yr, int call)
        {
            //This is a LEFT OUTER JOIN LINQ Query
            var ip_report = from data1 in db.ip_report
                            join data2 in db.implementing_partner
                            on data1.ip_id equals data2.ip_id into joinTable1
                            from a in joinTable1.DefaultIfEmpty()
                            join ipid in db.RFPs
                            on data1.RFP_id equals ipid.RFP_id into rfpJoinTable
                            from ipData in rfpJoinTable.DefaultIfEmpty()
                            join statusValue in db.report_status
                            on data1.report_status_id equals statusValue.report_status_id into statusJoinTable
                            from status in statusJoinTable.DefaultIfEmpty()
                            join data3 in db.report_period
                            on data1.report_period_id equals data3.report_period_id into joinTable2
                            from b in joinTable2.DefaultIfEmpty()
                            join data4 in db.years
                            on data1.year_id equals data4.year_id into joinTable3
                            from c in joinTable3.DefaultIfEmpty()
                            join data5 in db.success_story
                            on new { data1.ip_id, data1.year_id, data1.report_period_id }
                            equals new { data5.ip_id, data5.year_id, data5.report_period_id } into joinTable4
                            from d in joinTable4.DefaultIfEmpty()
                            where data1.ip_id == ip &&
                            data1.year_id == yr &&
                            data1.report_period_id == quarter &&
                            data1.RFP_id == call
                            select new
                            {
                                data1.implementing_partner.name,
                                b.period,
                                c.year1,
                                call_name = ipData.RFP_name,
                                region_name = a.region.name,
                                data1.project_context,
                                data1.beneficiaries,
                                d.title,
                                d.context,
                                d.problem,
                                d.intervention,
                                d.result,
                                data1.challenges,
                                data1.lessons,
                                data1.recommendations,
                                data1.project_editor,
                                data1.submission_date,
                                data1.upcoming_quarter,
                                data1.gender_mainstreaming,
                                data1.partnerships,

                                data1.approved_budget_cfs,
                                data1.total_cfs,
                                data1.balance_cfs,
                                data1.delivery_cfs,
                                status.status
                            };

            var donors = from data in db.donor_report_match
                         where data.ip_id == ip && 
                         data.year_id == yr && 
                         data.report_period_id == quarter &&
                         data.RFP_id == call
                         select new
                         {
                             data.donor.donor_id,
                             data.donor.name
                         };

            var ip_beneficiaries = from data1 in db.ip_direct_beneficiary
                                   join data2 in db.beneficiaries
                                   on data1.beneficiary_id equals data2.beneficiary_id
                                   where data1.ip_id == ip && 
                                   data1.year_id == yr && 
                                   data1.report_period_id == quarter &&
                                   data1.RFP_id == call
                                   select new
                                   {
                                       data2.name,
                                       data1.male,
                                       data1.female
                                   };

            var ip_results = from data1 in db.indicator_result
                             join data2 in db.indicators
                             on data1.indicator_id equals data2.indicator_id into JoinTable1
                             from _joinTable1 in JoinTable1.DefaultIfEmpty()
                             join data3 in db.indicator_status
                             on data1.status_id equals data3.status_id into JoinTable2
                             from _joinTable2 in JoinTable2.DefaultIfEmpty()
                             where data1.ip_id == ip && 
                             data1.year_id == yr && 
                             data1.report_period_id == quarter &&
                             data1.RFP_id == call
                             select new
                             {
                                 data1.indicator.name,
                                 data1.indicator.target,
                                 data1.indicator_id,
                                 data1.result,
                                 data1.comment,
                                 data1.status_id
                             };

            var upcomingQuarter = from data in db.upcoming_activities
                                  where data.ip_id == ip && 
                                  data.report_period_id == quarter && 
                                  data.year_id == yr && 
                                  data.RFP_id == call
                                  select new
                                  {
                                      data.activity,
                                      data.venue,
                                      data.date
                                  };

            var MonitoringAndEvaluation = from data in db.monitoring_and_evaluation
                                          where data.ip_id == ip &&
                                          data.report_period_id == quarter &&
                                          data.year_id == yr &&
                                          data.RFP_id == call
                                          select new
                                          {
                                              data.activity,
                                              data.observation,
                                              data.recommendation,
                                              data.action_taken
                                          };

            var risksAndMitigationMeasures = from data in db.risk_and_mitigation
                                             where data.ip_id == ip &&
                                             data.report_period_id == quarter &&
                                             data.year_id == yr &&
                                             data.RFP_id == call
                                             select new
                                             {
                                                 data.risk,
                                                 data.mitigation
                                             };

            string path = Server.MapPath("~/Amkeni_System/ip_photos/");
            var ipPhotos = from data in db.ip_photo
                           where data.ip_id == ip && 
                           data.report_period_id == quarter && 
                           data.year_id == yr && 
                           data.RFP_id == call
                           select new
                           {
                               data.photo_id,
                               data.caption,
                               name = "file:///" + path + data.name
                           };

            ReportViewer viewer = new ReportViewer();
            ReportDataSource rd = new ReportDataSource("DataSet1", ip_report);
            ReportDataSource ben = new ReportDataSource("DataSet2", ip_beneficiaries);
            ReportDataSource ind = new ReportDataSource("DataSet3", ip_results);
            ReportDataSource act = new ReportDataSource("DataSet4", upcomingQuarter);
            ReportDataSource photos = new ReportDataSource("DataSet5", ipPhotos);
            ReportDataSource _donors = new ReportDataSource("DataSet6", donors);
            ReportDataSource _monitoringAndEvaluation = new ReportDataSource("DataSet7", MonitoringAndEvaluation);
            ReportDataSource _riskAndMitigation = new ReportDataSource("DataSet8", risksAndMitigationMeasures);
            viewer.LocalReport.DataSources.Add(rd);
            viewer.LocalReport.DataSources.Add(ben);
            viewer.LocalReport.DataSources.Add(ind);
            viewer.LocalReport.DataSources.Add(act);
            viewer.LocalReport.DataSources.Add(photos);
            viewer.LocalReport.DataSources.Add(_donors);
            viewer.LocalReport.DataSources.Add(_monitoringAndEvaluation);
            viewer.LocalReport.DataSources.Add(_riskAndMitigation);
            viewer.LocalReport.EnableExternalImages = true;
            viewer.LocalReport.ReportPath = "Amkeni_System/Report/IPReport_Template.rdlc";

            //Variables
            Warning[] warnings;
            string[] streamIds;
            string mimeType = string.Empty;
            string encoding = string.Empty;
            string extension = string.Empty;
            string fileName = string.Empty;
            foreach (var metaData in ip_report)
            {
                fileName = metaData.status + "_" + metaData.name + "_" + metaData.period + "_" + metaData.year1.ToString();
            }

            byte[] bytes = viewer.LocalReport.Render("PDF", null, out mimeType, out encoding, out extension, out streamIds, out warnings);

            //After getting the bytes representing the PDF report, buffer it and send it to the client.
            Response.Buffer = true;
            Response.Clear();
            Response.ClearContent();
            Response.ClearHeaders();
            Response.AppendHeader("Content-Length", bytes.Length.ToString());
            Response.ContentType = mimeType;
            Response.AddHeader("content-disposition", "attachment:filename=" + fileName + "." + extension);
            Response.BinaryWrite(bytes); //Create the file
            Response.Flush(); //Send to client for download
        }
    }
}